package ServerOperation;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import Controllers.GlobalData;
import Controllers.GenericToolsForController.PDFfile;
import Controllers.GenericToolsForController.RequestMainStatusEnum;
import Controllers.GenericToolsForController.RequestStepStatusEnum;
import Entity.ActivityReport;
import Entity.DelayReport;
import Entity.Message;
import Entity.OperationReport;
import Entity.Report;
import Entity.User;
import Entity.Worker;

public class DBStatistical  implements IDBHandler 
{
	private Connection conn;
	
	public DBStatistical(Connection conn) 
	{
		super();
		this.conn = conn;
	}


	@Override
	public Message decipherMessage(Message msg)
	{
		Message AnswerMessage = null;
		int op = (msg.getOpCode()%1000);
	
		switch (op){
		case 1:
			AnswerMessage =  getActivityReport(msg);	
			break;
		case 2:
			AnswerMessage =  getOperationReport(msg);	
			break;
		case 3:
			AnswerMessage =  getDelayReport(msg);	
			break;
		case 4:
			AnswerMessage =  getAllActivityReport(msg);	
			break;	
		}
		return AnswerMessage;
	}

  /*
   * This Section is Abuot Activity Report
   * 
   */
	
	private Message getActivityReport(Message msg) 
	{
		Message AnswerMessage = null;
		
		if(msg.getDataGram() instanceof Object[])
		{
			Object[] objArray = (Object[])msg.getDataGram();
			if(objArray.length == 2 && objArray[0] instanceof Date && objArray[1] instanceof Date)
			{
				Date start = (Date)objArray[0];
				Date end = (Date)objArray[1];
				
				DBHandler dh  = DBHandler.getInstance();
				ReportBuilder rb = new ReportBuilder(dh.getConn());
				String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
				String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
				String path = getDefaultPath();	
				String filename = "ActivityReport"+startStr+"-"+endStr+".pdf";
				path+=filename;
				rb.createActivityReport(path, start, end);
				PDFfile pdffile = new PDFfile();
				pdffile.read(path);
				int reportId = addStatisticalReportToDB( path, filename);
               Worker icm = GenricOperation.getIcmManager(conn);
				Report activity = new ActivityReport(icm,reportId,path,pdffile);
				AnswerMessage = new Message(msg.getOpCode() + 1000, msg.getMessageID(), activity);
				return AnswerMessage;
                			
			}
		}
			
		return new Message(msg.getOpCode() + 1000, msg.getMessageID(), null);
	}
	
	
	String getDefaultPath()
	{
		//build the path to file
		String currentDir = System.getProperty("user.dir");
		String path = currentDir+"\\";
		File dir=new File(path);
		if(! dir.exists())
		{
			dir.mkdir();
			System.out.println("Creats new dir : "+path);
		}
		path+="reports" ;
		dir=new File(path);
		if(! dir.exists())
		{
			dir.mkdir();
			System.out.println("Creats new dir : "+path);
		}
		
		//we decide the file name
		System.out.println(path);
		//actually creates a copy of file
	    
	    System.out.println(path);
	    path = path.replace('\\' , '/');
	    System.out.println(path);
		return path;
	}
	
	public Object[] activityReportData(Date start,Date end)
	{
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		int numberOfCloseRequest = getNumberOfRequestsInPriodOfTimeByMainStatus(start,end,RequestMainStatusEnum.CLOSE);
		int numberOfOpenRequest = getNumberOfRequestsInPriodOfTimeByMainStatus(start,end,RequestMainStatusEnum.OPEN);
		int numberOfSuspendeRequest = getNumberOfRequestsInPriodOfTimeByMainStatus(start,end,RequestMainStatusEnum.SUSPEND);
		int numberOfRejectedRequest = getNumberOfRequestsInPriodOfTimeThatWereRejected(start,end);
		Map<Integer,Integer> mp = getTotalWorkingDaysForAllRequests(start,end);
		
		Object[] obj = new Object[]{
				new Integer(numberOfCloseRequest),
				new Integer(numberOfOpenRequest),
				new Integer(numberOfSuspendeRequest),
				new Integer(numberOfRejectedRequest),
				mp};
		return obj;
	}
	
	public int getNumberOfRequestsInPriodOfTimeByMainStatus(Date start,Date end, RequestMainStatusEnum mStstud)
	{
		int returnValue = 0;
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
		
		String SpecficQuery = new String(
					"SELECT COUNT(DISTINCT rh.reqID) " +
					"FROM history h, requesthistory rh , status s " +
					"WHERE h.id = rh.historyID " + 
					"AND ( " +
					"( (h.StartDate > '" + startStr + "') AND (h.StartDate < '" + endStr + "') )  OR "  +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + startStr + "') ) OR " +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + endStr + "') ) OR" +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate IS NULL) ) " + 
					") " +
					"AND h.Status = s.id AND s.MainStatus = ? ; " );
		
		System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, mStstud.getText());
			ResultSet rs = stmt.executeQuery();
			if (rs.next())
			{
				returnValue = rs.getInt(1);
			}
			stmt.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return returnValue;
	}
	
	public int getNumberOfRequestsInPriodOfTimeThatWereRejected(Date start,Date end)
	{
		int returnValue = 0;
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
		
		String SpecficQuery = new String(
					"SELECT COUNT(DISTINCT rh.reqID) " +
					"FROM history h, requesthistory rh , status s " +
					"WHERE h.id = rh.historyID " + 
					"AND ( " +
					"( (h.StartDate > '" + startStr + "') AND (h.StartDate < '" + endStr + "') )  OR "  +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + startStr + "') ) OR " +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + endStr + "') ) " +
					") " +
					"AND h.Status = s.id AND s.StepStatus = ? ; " );
		
		System.out.println(SpecficQuery);
		try 
		{
			PreparedStatement stmt = conn.prepareStatement(SpecficQuery);
			stmt.setString(1, RequestStepStatusEnum.REV_REVIEWED_CLOSE.getText());
			ResultSet rs = stmt.executeQuery();
			if (rs.next())
			{
				returnValue = rs.getInt(1);
			}
			stmt.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return returnValue;
	}
	
	/**
	 * return Map that : Key is request id, Value is number of hours
	 * @param start
	 * @param end
	 * @return
	 */
	public Map<Integer,Integer> getTotalWorkingDaysForAllRequests(Date start,Date end)
	{
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end); 
		 
		 String SpecficQuery = new String(
		        "SELECT reqID,SUM(Duration)" +
				"FROM ( " +
						"(SELECT rh.reqID,SUM(DATEDIFF(h.EndDate,h.StartDate)) AS 'Duration' " +
						"FROM history h, requesthistory rh , status s " + 
						"WHERE h.id = rh.historyID  " + 
						"AND h.Status = s.id AND s.MainStatus = 1  " + 
						"AND (h.EndDate <= '" + endStr + "' ) AND (h.StartDate   >= '" + startStr + "') " +
						"GROUP BY rh.reqID) " +
					"UNION " +
						"(SELECT rh.reqID,SUM(DATEDIFF(h.EndDate,'" + startStr + "')) AS 'Duration' " +
						"FROM history h, requesthistory rh , status s " +
						"WHERE h.id = rh.historyID " +
						"AND h.Status = s.id AND s.MainStatus = 1 " + 
				        "AND (h.StartDate < '" + startStr + "' ) " +
						"AND (h.EndDate  IS NOT NULL) AND (h.EndDate  <= '" + endStr + "') " +
						"AND (h.EndDate  >= '" + startStr + "') " + 
						"GROUP BY rh.reqID) " +
					"UNION " +
						"(SELECT rh.reqID,SUM(DATEDIFF('" + endStr + "',h.StartDate)) AS 'Duration' " +
						"FROM history h, requesthistory rh , status s " +
						"WHERE h.id = rh.historyID  " +
						"AND h.Status = s.id AND s.MainStatus = 1 " + 
				        "AND (h.StartDate < '" + endStr + "' ) AND (h.StartDate > '" + startStr + "' ) " +
						"AND ((h.EndDate IS NULL) OR (h.EndDate > '" + endStr + "')) " +
						"GROUP BY rh.reqID) " + 
					"UNION " +
						"(SELECT rh.reqID,SUM(DATEDIFF('" + endStr + "','" + startStr + "')) AS 'Duration' " +
						"FROM history h, requesthistory rh , status s " +
						"WHERE h.id = rh.historyID  " +
						"AND h.Status = s.id AND s.MainStatus = 1 " + 
				        "AND (h.StartDate < '" + startStr + "' )  " +
						"AND ((h.EndDate IS NULL) OR (h.EndDate > '" + endStr + "')) " +
						"GROUP BY rh.reqID) " +
				    ") as masho " +
				"GROUP BY masho.reqID" );
			
		Map<Integer,Integer> mp=new HashMap<Integer,Integer>();	
		 
		System.out.println(SpecficQuery);
		try 
		{
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SpecficQuery);
				while (rs.next())
				{
					int reqId = rs.getInt(1);
					int amount = rs.getInt(2);
					System.out.println("reqId: "  + reqId + " amount: " + amount);
					mp.put(reqId, amount);
				}
				stmt.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		 return mp;
	}
	
	
	
	
	  /*
	   * This Section is Abuot Operation Report
	   * 
	   */
	private Message getOperationReport(Message msg) 
	{
		Message AnswerMessage = null;
		
		if(msg.getDataGram() instanceof Object[])
		{
			Object[] objArray = (Object[])msg.getDataGram();
			if(objArray.length == 2 && objArray[0] instanceof Date && objArray[1] instanceof Date)
			{
				Date start = (Date)objArray[0];
				Date end = (Date)objArray[1];
				
				DBHandler dh  = DBHandler.getInstance();
				ReportBuilder rb = new ReportBuilder(dh.getConn());
				String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
				String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
				System.out.println("creating pdf file ActivityReport"+startStr+" to "+endStr);
				String path = getDefaultPath();	
				String filename = "OperationReport"+startStr+"-"+endStr+".pdf";
				path+=filename;
				rb.createOperationReport(path, start, end);
				PDFfile pdffile = new PDFfile();
				pdffile.read(path);
				int reportId = addStatisticalReportToDB( path,filename );
				 Worker icm = GenricOperation.getIcmManager(conn);
				 Report operation = new OperationReport(icm,reportId,path ,pdffile);
				AnswerMessage = new Message(msg.getOpCode() + 1000, msg.getMessageID(), operation);
				
				return AnswerMessage;
                
		}			 AnswerMessage = new Message(msg.getOpCode() + 1000, msg.getMessageID(), null);

		}
	
			
		return new Message(msg.getOpCode() + 1000, msg.getMessageID(), null);
	}
	public Object[] operationReportData(Date start,Date end)
	{
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		int numberOfAprrovalExetensios = getNumberOfAprrovalExetanction(start,end);
		int numberOfEnyExetensios = getNumberOfDenyExetanction(start,end);
		Map<Integer,Integer> fromStep2 = getTotalReturnFomStap2ForAllRequests(start,end);
		Map<Integer,Integer> fromStep4 = getTotalReturnFomStap4ForAllRequests(start,end);
		
		Object[] obj = new Object[]{
				new Integer(numberOfAprrovalExetensios),
				new Integer(numberOfEnyExetensios),
				fromStep2,
				fromStep4
		};
		return obj;
	}
	public int getNumberOfAprrovalExetanction(Date start,Date end)
	{
		int returnValue = 0;
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
		
		String SpecficQuery = new String(
					"SELECT COUNT(rh.reqID) " +
					"FROM history h, requesthistory rh , status s " +
					"WHERE h.id = rh.historyID " + 
					"AND ( " +
					"( (h.StartDate > '" + startStr + "') AND (h.StartDate < '" + endStr + "') )  OR "  +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + startStr + "') ) OR " +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + endStr + "') ) OR" +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate IS NULL) ) " + 
					") " +
					"AND h.Status = s.id AND s.MainStatus = 1 AND  " +
					"(s.StepStatus = 9 OR s.StepStatus = 15 OR s.StepStatus = 23 OR s.StepStatus = 27)" );
		
		System.out.println(SpecficQuery);
		try 
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(SpecficQuery);
			if (rs.next())
			{
				returnValue = rs.getInt(1);
			}
			stmt.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return returnValue;
	}	
	public int getNumberOfDenyExetanction(Date start,Date end)
	{
		int returnValue = 0;
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
		
		String SpecficQuery = new String(
					"SELECT COUNT(rh.reqID) " +
					"FROM history h, requesthistory rh , status s " +
					"WHERE h.id = rh.historyID " + 
					"AND ( " +
					"( (h.StartDate > '" + startStr + "') AND (h.StartDate < '" + endStr + "') )  OR "  +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + startStr + "') ) OR " +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + endStr + "') ) OR" +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate IS NULL) ) " + 
					") " +
					"AND h.Status = s.id AND s.MainStatus = 1 AND  " +
					"(s.StepStatus = 10 OR s.StepStatus = 16 OR s.StepStatus = 24 OR s.StepStatus = 28)" );
		
		System.out.println(SpecficQuery);
		try 
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(SpecficQuery);
			if (rs.next())
			{
				returnValue = rs.getInt(1);
			}
			stmt.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return returnValue;
	}
	public Map<Integer,Integer> getTotalReturnFomStap4ForAllRequests(Date start,Date end)
	{
		return  getTotalReturnFomStapForAllRequests(start,end,RequestStepStatusEnum.EXE_REXECUTE);
		
	}
	public Map<Integer,Integer> getTotalReturnFomStap2ForAllRequests(Date start,Date end)
	{
		return getTotalReturnFomStapForAllRequests(start,end,RequestStepStatusEnum.EVL_RETURNED_TO_REVALUTION);

	}
	public Map<Integer,Integer> getTotalReturnFomStapForAllRequests(Date start,Date end,RequestStepStatusEnum step)
	{
		Map<Integer,Integer> mp = new HashMap<Integer, Integer>();
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
		
		String SpecficQuery = new String(
					"SELECT rh.reqID,COUNT(*) " +
					"FROM history h, requesthistory rh , status s " +
					"WHERE h.id = rh.historyID " + 
					"AND ( " +
					"( (h.StartDate > '" + startStr + "') AND (h.StartDate < '" + endStr + "') )  OR "  +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + startStr + "') ) OR " +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate   > '" + endStr + "') ) OR" +
					"( (h.StartDate < '" + endStr + "' ) AND (h.EndDate IS NULL) ) " + 
					") " +
					"AND h.Status = s.id AND s.MainStatus = 1 AND s.StepStatus = "+ step.getText() + " " +
					"GROUP BY rh.reqID" );
		
		System.out.println(SpecficQuery);
		try 
		{
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SpecficQuery);
				while (rs.next())
				{
					int reqId = rs.getInt(1);
					int amount = rs.getInt(2);
					System.out.println("reqId: "  + reqId + " amount: " + amount);
					mp.put(reqId, amount);
				}
				stmt.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		 return mp;
		
	}

	
	 /*
	   * This Section is Abuot Delay report
	   * 
	   */
	
	private Message getDelayReport(Message msg) 
	{
		Message AnswerMessage = null;
		
		if(msg.getDataGram() instanceof Object[])
		{
			Object[] objArray = (Object[])msg.getDataGram();
			if(objArray.length == 2 && objArray[0] instanceof Date && objArray[1] instanceof Date)
			{
				Date start = (Date)objArray[0];
				Date end = (Date)objArray[1];
				
				DBHandler dh  = DBHandler.getInstance();
				ReportBuilder rb = new ReportBuilder(dh.getConn());
				String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
				String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
				String path = getDefaultPath();	
				String filename = "DelayReport"+startStr+"-"+endStr+".pdf";
				path+=filename;
				rb.createDelaysReport(path, start, end);
				PDFfile pdffile = new PDFfile();
				pdffile.read(path);
				int reportId = addStatisticalReportToDB( path, filename);
				 Worker icm = GenricOperation.getIcmManager(conn);
				 Report delay = new DelayReport(icm,reportId,path ,pdffile);
				AnswerMessage = new Message(msg.getOpCode() + 1000, msg.getMessageID(), delay);
				
				return AnswerMessage;
			}
			 AnswerMessage = new Message(msg.getOpCode() + 1000, msg.getMessageID(), null);
		}
			
		return new Message(msg.getOpCode() + 1000, msg.getMessageID(), null);
	}

	public Object[] DelayReportData(Date start,Date end)
	{
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		Map<Integer,String[]> mp = getNumbersOfDelayAndNumbersOfWorkingDaysForAllRequests(start,end);
		
		Object[] obj = new Object[]{
				mp
		};
		return obj;
	}
	
	public  Map<Integer,String[]> getNumbersOfDelayAndNumbersOfWorkingDaysForAllRequests(Date start,Date end)
	{
		if(end.getTime() > (new Date()).getTime())
			end = new Date();
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end); 
		 
		 String SpecficQuery = new String(
		        "SELECT reqID,Count(count), SUM(Duration),name " +
				"FROM ( " +
						"(SELECT rh.reqID,Count(rh.reqID) as count , SUM(DATEDIFF(h.EndDate,h.StartDate)) AS 'Duration',i.name " +
						"FROM history h, requesthistory rh , status s, request r, info_system i " + 
						"WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id " + 
						"AND h.Status = s.id AND s.MainStatus = 1  " + 
						"AND (h.EndDate <= '" + endStr + "' ) AND (h.StartDate   >= '" + startStr + "') " +
						"AND h.Comment LIKE 'Exception%' "+
						"GROUP BY rh.reqID) " +
					"UNION " +
				     	"(SELECT rh.reqID, Count(rh.reqID) as count ,SUM(DATEDIFF(h.EndDate,'" + startStr + "')) AS 'Duration',i.name  " +
						"FROM history h, requesthistory rh , status s, request r, info_system i " +
						"WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id " +
						"AND h.Status = s.id AND s.MainStatus = 1 " + 
				        "AND (h.StartDate < '" + startStr + "' ) " +
						"AND (h.EndDate  IS NOT NULL) AND (h.EndDate  <= '" + endStr + "') " +
						"AND (h.EndDate  >= '" + startStr + "') " + 
						"AND h.Comment LIKE 'Exception%' "+
						"GROUP BY rh.reqID) " +
					"UNION " +
						"(SELECT rh.reqID, Count(rh.reqID) as count,SUM(DATEDIFF('" + endStr + "',h.StartDate)) AS 'Duration' ,i.name   " +
						"FROM history h, requesthistory rh , status s ,request r, info_system i " +
						"WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id " +
						"AND h.Status = s.id AND s.MainStatus = 1 " + 
				        "AND (h.StartDate < '" + endStr + "' ) AND (h.StartDate > '" + startStr + "' ) " +
						"AND ((h.EndDate IS NULL) OR (h.EndDate > '" + endStr + "')) " +
						"AND h.Comment LIKE 'Exception%' "+
						"GROUP BY rh.reqID) " + 
					"UNION " +
						"(SELECT rh.reqID, Count(rh.reqID) as count,SUM(DATEDIFF('" + endStr + "','" + startStr + "')) AS 'Duration',i.name  " +
						"FROM history h, requesthistory rh , status s ,request r, info_system i " +
						"WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id   " +
						"AND h.Status = s.id AND s.MainStatus = 1 " + 
				        "AND (h.StartDate < '" + startStr + "' )  " +
						"AND ((h.EndDate IS NULL) OR (h.EndDate > '" + endStr + "')) " +
						"AND h.Comment LIKE 'Exception%' "+
						"GROUP BY rh.reqID) " +
				    ") as masho " +
				"GROUP BY masho.reqID" );
			
	 Map<Integer,String[]> mp = new HashMap<Integer, String[]>();  
		 
		System.out.println(SpecficQuery);
		try 
		{
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery(SpecficQuery);
				while (rs.next())
				{
					int reqId = rs.getInt(1);
					String countDeley = rs.getString(2);
					String amountDays = rs.getString(3);
					String infoName = rs.getString(4);
					System.out.println("reqId: "  + reqId + " countDeley: " + countDeley  + " amountDays: " + amountDays  + " infoName: " + infoName);
					mp.put(reqId, new String[]{countDeley,amountDays,infoName});
				}
				stmt.close();
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		return mp;	
	}
	
	
public int addStatisticalReportToDB(String filePath, String comment)
{
	int reportId = GenricOperation.getNextId("report",conn);
	int creator = 62;
	String SpecficQuery = new String("Insert Into report (id,File,comment,creator) Values ("+reportId+",'"+filePath+"','"+comment+"', "+creator+")");
	System.out.println(SpecficQuery);
	Statement stmt;
	try {
		stmt = conn.createStatement();
		stmt.executeUpdate(SpecficQuery);
		stmt.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return reportId;
}

private Message   getAllActivityReport(Message msg) 
{
		Message AnswerMessage = null;
	List<Report> allactivityReports = new ArrayList<Report>();
	String SpecficQuery = new String("SELECT id, File, creator, comment " +
		                       	" FROM report " +
		                       	"WHERE Comment LIKE 'ActivityReport%' ");
	System.out.println(SpecficQuery);
	Statement stmt;
	try {
		 stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(SpecficQuery);
		while (rs.next())
		{
			     PDFfile pdFfile = null;
				Worker worker = GenricOperation.getWorkerById(rs.getInt(3), conn);
				if (rs.getString(2) != null)
				{
				        pdFfile = new PDFfile();
				       System.out.println(rs.getString(2)) ;
			           pdFfile.read(rs.getString(2));
				       
				}    
				String myComment = null;
				       if (rs.getString(4) != null)
				       {
				    	   myComment = rs.getString(4);
				          
				    	   Report activityReport = new Report(	 worker, rs.getInt(1), null, myComment,pdFfile);  
				    	allactivityReports.add(activityReport);   
					}
			
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return new Message(msg.getOpCode()+1000,msg.getMessageID(),allactivityReports);
}
	
	public static void main(String[] args) 
	{
		DBHandler dh  = DBHandler.getInstance();
		DBStatistical de = new DBStatistical(dh.getConn());
		Date start = new Date(new Date().getTime() + 30*1000*24*60*60);
		Date end = new Date(new Date().getTime() -1*1000*24*60*60);
		String startStr = new SimpleDateFormat("yyyy-MM-dd").format(start);
		String endStr = new SimpleDateFormat("yyyy-MM-dd").format(end);
		
		System.out.println("start = " + startStr + " end " + endStr);
		int numberOfCloseRequest = de.getNumberOfRequestsInPriodOfTimeByMainStatus(start,end,RequestMainStatusEnum.CLOSE);
		int numberOfOpenRequest = de.getNumberOfRequestsInPriodOfTimeByMainStatus(start,end,RequestMainStatusEnum.OPEN);
		int numberOfSuspendeRequest = de.getNumberOfRequestsInPriodOfTimeByMainStatus(start,end,RequestMainStatusEnum.SUSPEND);
		int numberOfRejectedRequest = de.getNumberOfRequestsInPriodOfTimeThatWereRejected(start,end);
		System.out.println("there is a " + numberOfCloseRequest + " numberOfCloseRequest  ");
		System.out.println("there is a " + numberOfOpenRequest + " numberOfOpenRequest  ");
		System.out.println("there is a " + numberOfSuspendeRequest + " numberOfSuspendeRequest  ");
		System.out.println("there is a " + numberOfRejectedRequest + " numberOfRejectedRequest  ");
		Map<Integer,Integer> mp = de.getTotalWorkingDaysForAllRequests(start,end);
		System.out.println("Size : " + mp.size() );
		
		
		int numberOfAprrovalExetensios = de.getNumberOfAprrovalExetanction(start,end);
		System.out.println("there is a " + numberOfAprrovalExetensios + " numberOfAprrovalExetensios  ");
		int numberOfEnyExetensios = de.getNumberOfDenyExetanction(start,end);
		System.out.println("there is a " + numberOfEnyExetensios + " numberOfEnyExetensios  ");
		Map<Integer,Integer> fromStep2 = de.getTotalReturnFomStap2ForAllRequests(start,end);
		System.out.println("fromStep2 : " + fromStep2.size() );
		Map<Integer,Integer> fromStep4 = de.getTotalReturnFomStap4ForAllRequests(start,end);
		System.out.println("fromStep4 : " + fromStep4.size() );
		
		
		
		Map<Integer,String[]> from = de.getNumbersOfDelayAndNumbersOfWorkingDaysForAllRequests(start,end);
		System.out.println("from : " + from.size() );
		
	}


	/*
	 * SELECT rh.reqID , i.name
FROM history h, requesthistory rh , status s,request r, info_system i
WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id 
AND (  
( (h.StartDate > '2013-06-01') AND (h.StartDate < '2013-07-01') )  OR 
( (h.StartDate < '2013-07-01' ) AND (h.EndDate   > '2013-06-01') ) OR
( (h.StartDate < '2013-07-01' ) AND (h.EndDate   > '2013-07-01') ) OR
( (h.StartDate < '2013-07-01' ) AND (h.EndDate IS NULL) ) 
)
AND h.Status = s.id AND s.MainStatus = 1 AND h.Comment LIKE 'Exception%';



SELECT reqID,Count(count), SUM(Duration),name
FROM (
	   (SELECT rh.reqID,Count(rh.reqID)as count , SUM(DATEDIFF(h.EndDate,h.StartDate)) AS 'Duration',i.name
		FROM history h, requesthistory rh , status s,request r, info_system i
		WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id
		AND h.Status = s.id AND s.MainStatus = 1 
		AND (h.EndDate <= '2013-07-01' ) AND (h.StartDate   >= '2013-06-01')
		AND h.Comment LIKE 'Exception%'
		GROUP BY rh.reqID)
	UNION
		(SELECT rh.reqID,Count(rh.reqID) as count,SUM(DATEDIFF(h.EndDate,'2013-06-01')) AS 'Duration',i.name
		FROM history h, requesthistory rh , status s,request r, info_system i
		WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id
		AND h.Status = s.id AND s.MainStatus = 1 
        AND (h.StartDate < '2013-06-01' )
		AND (h.EndDate  IS NOT NULL) AND (h.EndDate  <= '2013-07-01')
		AND (h.EndDate  >= '2013-06-01') 
        AND (h.comment LIKE 'Exception%' )
		GROUP BY rh.reqID) 
	UNION
		(SELECT rh.reqID,Count(rh.reqID) as count,SUM(DATEDIFF('2013-07-01',h.StartDate)) AS 'Duration',i.name
		FROM history h, requesthistory rh , status s,request r, info_system i
		WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id
		AND h.Status = s.id AND s.MainStatus = 1 
        AND (h.StartDate < '2013-07-01' ) AND (h.StartDate > '2013-06-01' )
		AND ((h.EndDate IS NULL) OR (h.EndDate > '2013-07-01'))
		AND h.Comment LIKE 'Exception%'
		GROUP BY rh.reqID)
	UNION
		(SELECT rh.reqID,Count(rh.reqID) as count,SUM(DATEDIFF('2013-07-01','2013-06-01')) AS 'Duration',i.name
		FROM history h, requesthistory rh , status s,request r, info_system i
		WHERE h.id = rh.historyID AND r.id = rh.reqID AND r.infoSystem = i.id
		AND h.Status = s.id AND s.MainStatus = 1 
        AND (h.StartDate < '2013-06-01' ) 
		AND ((h.EndDate IS NULL) OR (h.EndDate > '2013-07-01'))
		AND h.Comment LIKE 'Exception%'
		GROUP BY rh.reqID)
    ) as masho
GROUP BY masho.reqID;
	 */
	
}

